<?php

require '../classes/lib/Slim/Slim.php';
Slim\Slim::registerAutoloader();
$app = new Slim\Slim();

/*
 * On GET /train
 * Get all train regardless of its status;
 */
$app->get('/train', function() use($app) {
            $sql = 'SELECT * FROM train;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $trains = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($trains) {
                    $app->response()->status(200);
                    echo json_encode($trains);
                } else {
                    $app->response()->status(404);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /train/active
 * get all train information that are active
 */
$app->get('/train/active', function() use($app) {
            $sql = 'SELECT trainId, trainName FROM train WHERE status = "ACV";';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $train = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($train) {
                    $app->response()->status(200);
                    echo json_encode($train);
                } else {
                    $app->response()->status(404);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /train
 * Create a new train in database
 */
$app->post('/train', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $train = json_decode($req->getBody());
            $sql = 'INSERT INTO train(trainName, status) VALUES (:tname, :status);';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('tname', $train->tname);
                $pstmt->bindParam('status', $train->status);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /train/:tid
 * Modify train details
 */
$app->put('/train/:tid', function($tid) use ($app) {
            $req = Slim\Slim::getInstance()->request();
            $train = json_decode($req->getBody());
            $sql = 'UPDATE train SET trainName = :tname, status = :status WHERE trainId = :tid;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('tname', $train->tname);
                $pstmt->bindParam('status', $train->status);
                $pstmt->bindParam('tid', $tid);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On DELETE /train/:tid
 * Deactivate train according to train id provided
 */
$app->delete('/train/:tid', function($tid) use($app) {
            $sql = 'UPDATE train SET status = "INV" WHERE trainId = :tid;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('tid', $tid);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /route/active
 * get all route information that are active
 */
$app->get('/route/active', function() use($app) {
            $sql = 'SELECT rid, routeName FROM route WHERE status = "ACV";';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $route = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($route) {
                    $app->response()->status(200);
                    echo json_encode($route);
                } else {
                    $app->response()->status(404);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /route
 * get all route information
 */
$app->get('/route', function() use($app) {
            $sql = 'SELECT * FROM route;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $route = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($route) {
                    $app->response()->status(200);
                    echo json_encode($route);
                } else {
                    $app->response()->status(404);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /routes
 * get all route information
 */
$app->get('/routes', function() use($app) {
            $sql = 'SELECT rid, routeName, status FROM route;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $route = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($route) {
                    $app->response()->status(200);
                    echo json_encode($route);
                } else {
                    $app->response()->status(404);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /route/:id
 * get route info by route id
 */
$app->get('/route/:id', function ($id) use($app) {
            $sql = 'SELECT * FROM route WHERE rid = :id;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('id', $id);
                $pstmt->execute();
                $route = $pstmt->fetchObject();
                closeConnection($dbcon);
                returnAsJSON($route, $app);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /route
 * Create new route
 */
$app->post('/route', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $route = json_decode($req->getBody());
            $sqlroute = 'INSERT INTO route(routeName, status) VALUES(:routename, :status);';
            $sqlstation = 'INSERT INTO stationinroute(sid, rid, travelTime) VALUES ';
            $fstation = json_decode($route->fstation);
            $bstation = json_decode($route->bstation);
            $forward = false;
            $backward = false;
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmtfroute = $dbcon->prepare($sqlroute);
                $pstmtfroute->bindParam('routename', $route->froute);
                $pstmtfroute->bindParam('status', $route->status);
                $pstmtfroute->execute();
                if ($pstmtfroute->rowCount() > 0) {
                    $pstmtfroute->closeCursor();
                    $rid = $dbcon->lastInsertId();
                    $array = array();
                    foreach ($fstation as $jsonObj) {
                        $array[] = '(' . $jsonObj->sid . ', ' . $rid . ', "' . $jsonObj->time . '"' . ')';
                    }
                    $pstmtstation = $dbcon->prepare($sqlstation . implode(', ', $array) . ';');
                    $pstmtstation->execute();
                    if ($pstmtstation->rowCount() > 0) {
                        $pstmtstation->closeCursor();
                        $forward = true;
                    }
                }
                $pstmtbroute = $dbcon->prepare($sqlroute);
                $pstmtbroute->bindParam('routename', $route->broute);
                $pstmtbroute->bindParam('status', $route->status);
                $pstmtbroute->execute();
                if ($pstmtbroute->rowCount() > 0) {
                    $rid = $dbcon->lastInsertId();
                    $array = array();
                    foreach ($bstation as $jsonObj) {
                        $array[] = '(' . $jsonObj->sid . ', ' . $rid . ', "' . $jsonObj->time . '"' . ')';
                    }
                    $pstmtbstation = $dbcon->prepare($sqlstation . implode(', ', $array) . ';');
                    $pstmtbstation->execute();
                    if ($pstmtbstation->rowCount() > 0) {
                        $backward = true;
                    }
                }
                if ($forward && $backward) {
                    echo json_encode('Routes successfully added.');
                    $app->response()->status(200);
                } else {
                    echo json_encode('Unable to create routes now, please try again later.');
                    $app->response()->status(404);
                }
                $dbcon->commit();
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /route/:rid
 * Update data of existing route
 */
$app->put('/route/:rid', function($rid) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $route = json_decode($req->getBody());
            $sql = 'UPDATE route SET routeName = :routename, status = :status WHERE rid = :rid;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('routename', $route->routeName);
                $pstmt->bindParam('status', $route->status);
                $pstmt->bindParam('rid', $rid);
                $pstmt->execute();
                if ($pstmt->rowCount() >= 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /route/station/:rid
 * update route and station details;
 */

$app->put('/route/station/:rid', function($rid) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $route = json_decode($req->getBody());
            $routesql = 'UPDATE route SET routeName = :routename, status = :status WHERE rid = :rid;';
            $deletesql = 'DELETE FROM stationinroute WHERE rid = :rid;';
            $insertsql = 'INSERT INTO stationinroute(sid, rid, travelTime) VALUES ';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($routesql);
                $pstmt->bindParam('routename', $route->routeName);
                $pstmt->bindParam('status', $route->status);
                $pstmt->bindParam('rid', $rid);
                $pstmt->execute();
                if ($pstmt->rowCount() >= 0) {
                    $pstmtss = $dbcon->prepare($deletesql);
                    $pstmtss->bindParam('rid', $rid);
                    $pstmtss->execute();
                    if ($pstmtss->rowCount() > 0) {
                        $array = array();
                        $stations = json_decode($route->stations);
                        foreach ($stations as $jsonObj) {
                            $array[] = '(' . $jsonObj->sid . ', ' . $rid . ', "' . $jsonObj->time . '"' . ')';
                        }
                        $pstmts = $dbcon->prepare($insertsql . implode(',', $array) . ';');
                        $pstmts->execute();
                        if ($pstmts->rowCount() > 0) {
                            $app->response()->status(200);
                        } else {
                            echo json_encode("unable to insert");
                            $app->response()->status(404);
                        }
                    } else {
                        $app->response()->status(404);
                        echo json_encode("unable to delete");
                    }
                } else {
                    $app->response()->status(404);
                    echo json_encode("unable to update");
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On DELETE /route/:rid
 * Delete existing route
 */
$app->delete('/route/:rid', function($rid) use($app) {
            $sql = 'UPDATE route SET status = "INV" WHERE rid = :rid;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('rid', $rid);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /schedule
 * Get all schedule's details, whether is active or not
 */
$app->get('/schedule', function() use($app) {
            $sql = 'SELECT shid, scheduleName, departureTime, s.status, r.rid, routeName, 
                t.trainId, trainName FROM schedule s, route r, train t WHERE s.rid = r.rid AND s.trainId = t.trainId;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $schedules = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($schedules) {
                    $app->response()->status(200);
                    echo json_encode($schedules);
                } else {
                    $app->response()->status(404);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /schedule/active
 * Get all schedules that are active
 */

$app->get('/schedule/active', function() use($app) {
            $sql = 'SELECT shid, scheduleName FROM schedule WHERE status = "ACV";';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $schedules = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($schedules) {
                    $app->response()->status(200);
                    echo json_encode($schedules);
                } else {
                    $app->response()->status(404);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /schedule
 * Create a new schedule in the database
 */
$app->post('/schedule', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $schedule = json_decode($req->getBody());
            $sql = 'INSERT INTO schedule(scheduleName, departureTime, status, rid, trainId)
               VALUES (:sname, :dtime, :status, :rid, :tid);';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('sname', $schedule->sname);
                $pstmt->bindParam('dtime', $schedule->dtime);
                $pstmt->bindParam('status', $schedule->status);
                $pstmt->bindParam('rid', $schedule->rid);
                $pstmt->bindParam('tid', $schedule->tid);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /schedule/shid
 * Update specific schedule details
 */
$app->put('/schedule/:shid', function($shid) use ($app) {
            $req = Slim\Slim::getInstance()->request();
            $schedule = json_decode($req->getBody());
            $sql = 'UPDATE schedule SET scheduleName = :sname, departureTime = :dtime,
                status = :status, rid = :rid, trainId = :tid
                WHERE shid = :shid;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('sname', $schedule->sname);
                $pstmt->bindParam('dtime', $schedule->dtime);
                $pstmt->bindParam('status', $schedule->status);
                $pstmt->bindParam('rid', $schedule->rid);
                $pstmt->bindParam('tid', $schedule->tid);
                $pstmt->bindParam('shid', $shid);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /login
 * login API for user to login
 */
$app->post('/login', function() use($app) {
            $salt = "A3/-osv$0-231@#SC34zsd8fq]tj34&41dsjyj70u2%7vw4>[:l-==";
            $req = Slim\Slim::getInstance()->request();
            $user = json_decode($req->getBody());
            $sql = 'SELECT uid, password, groupId FROM users WHERE uid = :uid';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('uid', $user->uid);
                $pstmt->execute();
                $users = $pstmt->fetchObject();
                if ($users && $users->groupId == 1) {
                    $hashed = hash('sha256', $salt . $user->password);
                    if ($hashed == $users->password) {
                        $result = array(
                            'login' => TRUE
                        );
                        echo json_encode($result);
                    } else {
                        $result = array(
                            'login' => FALSE,
                            'err' => 'pass',
                            'message' => 'Password did not match.'
                        );
                        echo json_encode($result);
                        $app->response()->status(404);
                    }
                } else {
                    $result = array(
                        'login' => FALSE,
                        'err' => 'uid',
                        'message' => 'User not found'
                    );
                    echo json_encode($result);
                    $app->response()->status(404);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /ticket/check/:code
 * Ticket validating API for checking if ticket is valid
 */

$app->get('/ticket/check/:code', function($code) use($app) {
            $sql = 'SELECT t.referenceNo, departureDate, departure, arrival, origin, destination, class, status FROM ticket t, ticketqrcode tq WHERE t.referenceNo = tq.referenceNo AND code = :code;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('code', $code);
                $pstmt->execute();
                $ticket = $pstmt->fetchObject();
                closeConnection($dbcon);
                returnAsJSON($ticket, $app);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /ticket/update/:referenceno
 * Update ticket status if it's a valid ticket
 */

$app->put('/ticket/update/:referenceno', function($referenceno) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $ticket = json_decode($req->getBody());
            $sql = 'UPDATE ticket SET status = :status WHERE referenceNo = :referenceno;';
            try {
                $dbcon = getConnection();

                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('status', $ticket->status);
                $pstmt->bindParam('referenceno', $referenceno);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    echo json_encode(array(
                        'updated' => TRUE
                    ));
                    $app->response()->status(200);
                } else {
                    echo json_encode(array(
                        'updated' => FALSE,
                        'message' => 'Ticket not updated'
                    ));
                    $app->response()->status(404);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                echo json_encode(array(
                    'updated' => FALSE,
                    'message' => $ex->getMessage()
                ));
                $app->response()->status(500);
            }
        }
);
/*
 * On GET /seat/coach/:coach
 * Get list of seat of specify coach with empty and occupied indicator
 */
$app->get('/seat/coach/:uid/:coach', function($uid, $coach) use($app) {
            $sql = 'SELECT uid FROM timetable t, staff s WHERE t.icNo = s.icNo AND s.uid = :uid AND scheduleDate = CURDATE()';
            $sql2 = 'SELECT seatid, seatNo FROM seat WHERE coach = :coach;';
            $sql3 = 'SELECT bs.seatid, bs.status
                    FROM bookedseat bs, seat s, schedule sh 
                    WHERE bs.seatid = s.seatid 
                    AND sh.shid = bs.shid 
                    AND coach = :coach 
                    AND NOW() 
                    BETWEEN CONCAT(CURDATE(), " ", departureTime) 
                    AND DATE_ADD(CONCAT(CURDATE(), " ", departureTime), INTERVAL 3 HOUR) 
                    AND bookedDate = CURDATE();';

            try {
                $ticket = array();
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('uid', $uid);
                $pstmt->execute();
                $timetable = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($timetable) {
                    $pstmt2 = $dbcon->prepare($sql2);
                    $pstmt2->bindParam('coach', $coach);
                    $pstmt2->execute();
                    $seat = $pstmt2->fetchAll(PDO::FETCH_ASSOC);
                    $pstmt3 = $dbcon->prepare($sql3);
                    $pstmt3->bindParam('coach', $coach);
                    $pstmt3->execute();
                    $occupiedseat = $pstmt3->fetchAll(PDO::FETCH_ASSOC);
                    $status = 'EMP';
                    $free = true;
                    for ($i = 0; $i < count($seat); $i++) {
                        for ($j = 0; $j < count($occupiedseat); $j++) {
                            if ($seat[$i]['seatid'] == $occupiedseat[$j]['seatid']) {
                                $free = false;
                                $status = $occupiedseat[$j]['status'];
                                break;
                            } else {
                                $free = true;
                                $status = 'EMP';
                            }
                        }
                        $ticket[] = array(
                            'seatno' => $seat[$i]['seatNo'],
                            'status' => $status,
                            'free' => $free
                        );
                    }
                } else {
                    $ticket = array(
                        'error' => true,
                        'message' => 'You do not have any assigned schedule at this time.'
                    );
                }
                closeConnection($dbcon);
                returnAsJSON($ticket, $app);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /staff/name/:uid
 * Get staff's first name
 */

$app->get('/staff/name/:uid', function($uid) use($app) {
            $sql = 'SELECT firstname FROM staff WHERE uid = :uid;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('uid', $uid);
                $pstmt->execute();
                $name = $pstmt->fetch(PDO::FETCH_COLUMN);
                if ($name) {
                    echo json_encode(array(
                        'error' => FALSE,
                        'name' => $name
                    ));
                    $app->response()->status(200);
                } else {
                    echo json_encode(array(
                        'error' => TRUE,
                        'message' => 'User not found'
                    ));
                }
            } catch (PDOException $ex) {
                echo json_encode(array(
                    'error' => true,
                    'message' => $ex->getMessage()
                ));
                $app->response()->status(500);
            }
        }
);

/*
 * On GET /timetable/:uid
 * Get time table assigned to the staff today
 */
$app->get('/timetable/:uid', function($uid) use($app) {
            $sql = 'SELECT scheduleName, ' .
                    'CONCAT(CURDATE(), " ", departureTime) AS departuretime, ' .
                    'DATE_ADD(CONCAT(curdate(), " ", departureTime), INTERVAL 3 HOUR) AS arrivaltime ' .
                    'FROM staff s, timetable t, schedule sh ' .
                    'WHERE t.icNo = s.icNo ' .
                    'AND sh.shid = t.shid ' .
                    'AND uid = :uid ' .
                    'AND scheduleDate = CURDATE();';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('uid', $uid);
                $pstmt->execute();
                $timetable = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($timetable) {
                    echo json_encode(array(
                        'error' => false,
                        'schedule' => $timetable
                    ));
                } else {
                    echo json_encode(array(
                        'error' => true,
                        'norecord' => TRUE,
                        'message' => "You do not have any schedule being assigned today."
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /timetables/:uid
 * Get all timetable assigned to that staff
 */

$app->get('/timetables/:uid', function($uid) use($app) {
            $sql = 'SELECT tableId, uid, t.shid, scheduleName, scheduleDate 
                FROM staff s, timetable t, schedule sh 
                WHERE t.icNo = s.icNo AND t.shid = sh.shid 
                AND uid = :uid AND scheduleDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 
                AND DATE_ADD(CURDATE(), INTERVAL 1 MONTH);';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('uid', $uid);
                $pstmt->execute();
                $timetables = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($timetables) {
                    $app->response()->status(200);
                    echo json_encode($timetables);
                } else {
                    $app->response()->status(404);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /timetables/:uid
 * Create timetables for specific staff
 */
$app->post('/timetables/:uid', function($uid) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $timetables = json_decode($req->getBody());
            $sql = 'INSERT INTO timetable(icNo, shid, scheduleDate) VALUES ';
            $selectIc = '(SELECT icNo FROM staff WHERE uid = :uid)';
            $insertquery = array();
            try {
                $shid = $timetables->shid;
                $dates = json_decode($timetables->dates);
                foreach ($dates as $value) {
                    $insertquery[] = '(' . $selectIc . ', ' . $shid . ', "' . $value->date . '")';
                }
                $dbcon = getConnection();
                $sql .= implode(', ', $insertquery) . ';';
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('uid', $uid);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(200);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /timetable/:tid
 * Update specific timetable schedule
 */
$app->put('/timetable/:tid', function($tid) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $timetable = json_decode($req->getBody());
            $sql = 'UPDATE timetable SET icNo = (SELECT icNo FROM staff WHERE uid = :uid), shid = :shid, scheduleDate = :date WHERE tableId = :tid;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('uid', $timetable->uid);
                $pstmt->bindParam('shid', $timetable->shid);
                $pstmt->bindParam('date', $timetable->date);
                $pstmt->bindParam('tid', $tid);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On DELETE /timetable/:tid
 * Delete timetable from the database
 */
$app->delete('/timetable/:tid', function($tid) use($app) {
            $sql = 'DELETE FROM timetable WHERE tableId = :tid;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('tid', $tid);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(200);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /discount
 * Get all available discount regardless of its expire date
 */
$app->get('/discount', function() use($app) {
            $sql = "SELECT discountId, type, rate, startDate, endDate FROM discountrate WHERE discountId != 1 AND status != 'DEL';";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $discount = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($discount) {
                    $app->response()->status(200);
                    echo json_encode($discount);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'No discount promotion found.'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On DELETE /discount/:id
 * Delete specific discount rate
 */

$app->delete('/discount/:id', function($id) use ($app) {
            $sql = 'UPDATE discountrate SET status = "DEL" WHERE discountId = :id';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('id', $id);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /discount
 * Create a new promotion rate
 */
$app->post('/discount', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $discount = json_decode($req->getBody());
            $sql = 'INSERT INTO discountrate(rate, type, startDate, endDate, status) VALUES(:rate, :type, :startdate, :enddate, "NDL");';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('rate', $discount->rate);
                $pstmt->bindParam('type', $discount->desc);
                $pstmt->bindParam('startdate', $discount->start);
                $pstmt->bindParam('enddate', $discount->end);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /discount/:did
 * Update discount
 */
$app->put('/discount/:did', function($did) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $discount = json_decode($req->getBody());
            $sql = 'UPDATE discountrate 
                SET rate = :rate, type = :type, startDate = :start, endDate = :end 
                WHERE discountId = :did;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('rate', $discount->rate);
                $pstmt->bindParam('type', $discount->desc);
                $pstmt->bindParam('start', $discount->start);
                $pstmt->bindParam('end', $discount->end);
                $pstmt->bindParam('did', $did);
                $pstmt->execute();
                if ($pstmt->rowCount() >= 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /redeemableitem
 * Get all redeemable items from database
 */
$app->get('/redeemableitem', function() use($app) {
            $sql = 'SELECT * FROM redeemableitem';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $items = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($items) {
                    $app->response()->status(200);
                    echo json_encode($items);
                } else {
                    $app->response()->status(200);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /redeemableitem
 * Create a new redeemable item
 */
$app->post('/redeemableitem', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $item = json_decode($req->getBody());
            $sql = 'INSERT INTO redeemableitem(itemCode, itemName, pointRequired, value, itemType, itemImage) 
                VALUES(:code, :name, :point, :value, :type, :image);';
            if (isset($item->imagedata)) {
                $imagefile = base64_decode($item->imagedata);
                if (file_put_contents('../upload/' . $item->imagename, $imagefile)) {
                    try {
                        $filepath = 'upload/' . $item->imagename;
                        $dbcon = getConnection();
                        $dbcon->beginTransaction();
                        $pstmt = $dbcon->prepare($sql);
                        $pstmt->bindParam('code', $item->code);
                        $pstmt->bindParam('name', $item->name);
                        $pstmt->bindParam('point', $item->point);
                        $pstmt->bindParam('value', $item->value);
                        $pstmt->bindParam('type', $item->type);
                        $pstmt->bindParam('image', $filepath);
                        $pstmt->execute();
                        if ($pstmt->rowCount() > 0) {
                            $app->response()->status(200);
                        } else {
                            $app->response()->status(404);
                        }
                        $dbcon->commit();
                        closeConnection($dbcon);
                    } catch (PDOException $ex) {
                        unlink('../upload/' . $item->imagename);
                        $dbcon->rollBack();
                        printError($ex->getMessage(), 500, $app);
                    }
                } else {
                    $app->response()->status(404);
                    echo json_encode("Unable to create item now, please try again later.");
                }
            } else {
                try {
                    $null = null;
                    $dbcon = getConnection();
                    $dbcon->beginTransaction();
                    $pstmt = $dbcon->prepare($sql);
                    $pstmt->bindParam('code', $item->code);
                    $pstmt->bindParam('name', $item->name);
                    $pstmt->bindParam('point', $item->point);
                    $pstmt->bindParam('value', $item->value);
                    $pstmt->bindParam('type', $item->type);
                    $pstmt->bindParam('image', $null, PDO::PARAM_NULL);
                    $pstmt->execute();
                    if ($pstmt->rowCount() > 0) {
                        $app->response()->status(200);
                    } else {
                        $app->response()->status(404);
                    }
                    $dbcon->commit();
                    closeConnection($dbcon);
                } catch (PDOException $ex) {
                    $dbcon->rollBack();
                    printError($ex->getMessage(), 500, $app);
                }
            }
        }
);

/*
 * On DELETE /redeemableitem/:icode
 * Delete the redeemable item based on the itemcode and remove the uploaded file
 */
$app->delete('/redeemableitem/:icode', function($icode) use($app) {
            $sql = 'DELETE FROM redeemableitem WHERE itemCode = :icode;';
            $sql2 = 'SELECT itemImage FROM redeemableitem WHERE itemCode = :icode;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql2);
                $pstmt->bindParam('icode', $icode);
                $pstmt->execute();
                $file = $pstmt->fetch(PDO::FETCH_OBJ);
                $pstmt2 = $dbcon->prepare($sql);
                $pstmt2->bindParam('icode', $icode);
                $pstmt2->execute();
                if ($pstmt2->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                if (isset($file->itemImage)) {
                    unlink('../' . $file->itemImage);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /redeemableitem/:icode
 * Update a redeemable item with provided details;
 */
$app->put('/redeemableitem/:icode', function($icode) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $item = json_decode($req->getBody());
            $sqlwithimage = 'UPDATE redeemableitem 
                SET itemName = :iname, pointRequired = :point,
                value = :value, itemType = :type, itemImage = :image
                WHERE itemCode = :icode;';
            $sqlwithoutimage = 'UPDATE redeemableitem 
                SET itemName = :iname, pointRequired = :point,
                value = :value, itemType = :type
                WHERE itemCode = :icode;';
            if (isset($item->imagedata)) {
                $imagefile = base64_decode($item->imagedata);
                if (file_put_contents('../upload/' . $item->imagename, $imagefile)) {
                    try {
                        $filepath = 'upload/' . $item->imagename;
                        $dbcon = getConnection();
                        $dbcon->beginTransaction();
                        $pstmt = $dbcon->prepare($sqlwithimage);
                        $pstmt->bindParam('icode', $icode);
                        $pstmt->bindParam('iname', $item->name);
                        $pstmt->bindParam('point', $item->point);
                        $pstmt->bindParam('value', $item->value);
                        $pstmt->bindParam('type', $item->type);
                        $pstmt->bindParam('image', $filepath);
                        $pstmt->execute();
                        if ($pstmt->rowCount() > 0) {
//                            unlink('../' . $item->previousfile);
                            $app->response()->status(200);
                        } else {
                            $app->response()->status(404);
                        }
                        $dbcon->commit();
                        closeConnection($dbcon);
                    } catch (PDOException $ex) {
                        unlink('../upload/' . $item->imagename);
                        $dbcon->rollBack();
                        printError($ex->getMessage(), 500, $app);
                    }
                } else {
                    $app->response()->status(404);
                    echo json_encode("Unable to create item now, please try again later.");
                }
            } else {
                try {
                    $dbcon = getConnection();
                    $dbcon->beginTransaction();
                    $pstmt = $dbcon->prepare($sqlwithoutimage);
                    $pstmt->bindParam('icode', $icode);
                    $pstmt->bindParam('iname', $item->name);
                    $pstmt->bindParam('point', $item->point);
                    $pstmt->bindParam('value', $item->value);
                    $pstmt->bindParam('type', $item->type);
                    $pstmt->execute();
                    if ($pstmt->rowCount() > 0) {
                        $app->response()->status(200);
                    } else {
                        $app->response()->status(404);
                    }
                    $dbcon->commit();
                    closeConnection($dbcon);
                } catch (PDOException $ex) {
                    $dbcon->rollBack();
                    printError($ex->getMessage(), 500, $app);
                }
            }
        }
);

/*
 * On GET /redeemeditem/:ref
 * Get specific redeemed item
 */
$app->get('/redeemeditem/:ref', function($ref) use($app) {
            $sql = 'SELECT referenceNo FROM redeemeditem WHERE referenceNo = :ref AND status = "UNUSED";';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('ref', $ref);
                $pstmt->execute();
                $item = $pstmt->fetch(PDO::FETCH_ASSOC);
                if ($item) {
                    $app->response()->status(200);
                    echo json_encode($item);
                } else {
                    $app->response()->status(404);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /redeemeditem/:ref
 * update redeemed item's status
 */
$app->put('/redeemeditem/:ref', function($ref) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $item = json_decode($req->getBody());
            $sql = 'UPDATE redeemeditem SET status = :status WHERE referenceNo = :ref;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('status', $item->status);
                $pstmt->bindParam('ref', $ref);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /station
 * Get all station regardless of its status
 */
$app->get('/station', function() use($app) {
            $sql = 'SELECT * FROM station';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $stations = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($stations) {
                    $app->response()->status(200);
                    echo json_encode($stations);
                } else {
                    $app->response()->status(404);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /station/active
 * Get all station that are currently active
 */
$app->get('/station/active', function() use($app) {
            $sql = 'SELECT * FROM station WHERE status = "ACV"';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $stations = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($stations) {
                    $app->response()->status(200);
                    echo json_encode($stations);
                } else {
                    $app->response()->status(404);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /station/:sid
 * Get station by the sid regardless of whether it is active or not
 */
$app->get('/station/:sid', function($sid) use($app) {
            $sql = 'SELECT * FROM station WHERE sid = :sid';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('sid', $sid);
                $pstmt->execute();
                $station = $pstmt->fetchObject();
                if ($station) {
                    $app->response()->status(200);
                    echo json_encode($station);
                } else {
                    $app->response()->status(404);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /station
 * Create a new station with provided details
 */

$app->post('/station', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $station = json_decode($req->getBody());
            $sql = 'INSERT INTO station(stationName, location, status) VALUES(:name, :location, :status);';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('name', $station->name);
                $pstmt->bindParam('location', $station->location);
                $pstmt->bindParam('status', $station->status);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /station/status/:sid
 * Update station's status with specify sid
 */

$app->put('/station/status/:sid', function($sid) use ($app) {
            $req = Slim\Slim::getInstance()->request();
            $station = json_decode($req->getBody());
            $sql = 'UPDATE station SET status = :status WHERE sid = :sid;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('sid', $sid);
                $pstmt->bindParam('status', $station->status);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);


/*
 * On GET /station/fare/:rid
 * Get specific's route's station's fare
 */
$app->get('/station/fare/:rid', function($rid) use($app) {
            $sql = 'SELECT sr.sid, stationName, fare FROM station s, stationinroute sr WHERE sr.sid = s.sid AND rid = :rid;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('rid', $rid);
                $pstmt->execute();
                $fare = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($fare) {
                    $app->response()->status(200);
                    echo json_encode($fare);
                } else {
                    $app->response()->status(404);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /staff/:id
 * Check existence of staff
 */
$app->get('/staff/:id', function($id) use($app) {
            $sql = 'SELECT icNo FROM staff WHERE uid = :id;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('id', $id);
                $pstmt->execute();
                $staff = $pstmt->fetch();
                if ($staff) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /staff/:id
 * Get ic of staff
 */
$app->get('/staffIC/:id', function($id) use($app) {
            $sql = 'SELECT icNo FROM staff WHERE uid = :id;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('id', $id);
                $pstmt->execute();
                $staff = $pstmt->fetch();
                if ($staff) {
                    $app->response()->status(200);
                    echo json_encode($staff);
                } else {
                    $app->response()->status(404);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /staff/:id
 * get staff info by user id
 */
$app->get('/searchStaffById/:id', function ($id) use($app) {
            $sql = 'SELECT * FROM staff s, users u WHERE s.uid = u.uid AND s.uid = :id AND status = "Available";';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('id', $id);
                $pstmt->execute();
                $staff = $pstmt->fetchObject();
                closeConnection($dbcon);
                echo json_encode($staff);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /searchStaffByIc/:ic
 * get staff info by user ic
 */
$app->get('/searchStaffByIc/:ic', function ($ic) use($app) {
            $sql = 'SELECT * FROM staff s, users u WHERE s.uid = u.uid AND icNo = :ic AND status = "Available";';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('ic', $ic);
                $pstmt->execute();
                $staff = $pstmt->fetchObject();
                closeConnection($dbcon);
                echo json_encode($staff);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /searchStaffExistByIc/:ic
 * get staff info by user ic
 */
$app->get('/searchStaffExistByIc/:ic', function ($ic) use($app) {
            $sql = 'SELECT * FROM staff s, users u WHERE s.uid = u.uid AND icNo = :ic;';
            $result = array();
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('ic', $ic);
                $pstmt->execute();
                $staff = $pstmt->fetchObject();
                if ($staff) {
                    $result = array(
                        'exist' => 'YES'
                    );
                } else {
                    $result = array(
                        'exist' => 'NO'
                    );
                }
                closeConnection($dbcon);
                echo json_encode($result);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);


$app->get('/searchStaffExistByUsername/:username', function ($username) use($app) {
            $sql = 'SELECT * FROM users u WHERE username = :username;';
            $result = array();
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('username', $username);
                $pstmt->execute();
                $staff = $pstmt->fetchObject();
                if ($staff) {
                    $result = array(
                        'exist' => 'YES'
                    );
                } else {
                    $result = array(
                        'exist' => 'NO'
                    );
                }
                closeConnection($dbcon);
                echo json_encode($result);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /staff/:id
 * Update staff data of existing status to DELETE
 */
$app->put('/deleteStaffById/:id', function($id) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $staff = json_decode($req->getBody());
            $sql = 'UPDATE staff SET status = :status WHERE uid = :id;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('status', $staff->status);
                $pstmt->bindParam('id', $id);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    returnAsJSON('staff', $staff, $app);
                } else {
                    printError('Staff not found', 404, $app);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /staff/:ic
 * Update staff data of existing status to DELETE
 */
$app->put('/deleteStaffByIc/:ic', function($ic) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $staff = json_decode($req->getBody());
            $sql = 'UPDATE staff SET status = :status WHERE icNo = :ic;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('status', $staff->status);
                $pstmt->bindParam('ic', $ic);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    returnAsJSON('staff', $staff, $app);
                } else {
                    printError('Staff not found', 404, $app);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /staff/:id
 * Update data of existing staff
 */
$app->put('/updateStaffById/:id', function($id) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $staff = json_decode($req->getBody());
            $sqlwithoutImage = 'UPDATE staff SET firstname = :firstname , lastname = :lastname , 
                    gender = :gender , address = :address , city = :city , state = :state , 
                    postalCode = :postalCode , contactNo =:contactNo ,  dob = :dob , position = :position, 
                    yearEmployed = :yearEmployed , status =:status
                    WHERE uid = :id';
            $sqlwithImage = 'UPDATE staff SET firstname = :firstname , lastname = :lastname , 
                    gender = :gender , address = :address , city = :city , state = :state , 
                    postalCode = :postalCode , contactNo =:contactNo ,  dob = :dob , position = :position ,
                    yearEmployed = :yearEmployed , profilePicture = :profilePicture , status =:status
                    WHERE uid = :id';
            $sqlU = 'UPDATE users SET email = :email , question = :question , answer = :answer WHERE uid = :id;';
            if(isset($staff->imagedata)){
                $imagefile = base64_decode($staff->imagedata);
                $filepath = 'upload/profilepicture/' . $staff->imagename;
            }
            try {
                $dbcon = getConnection();
                if(isset($staff->imagedata)){
                    $pstmt = $dbcon->prepare($sqlwithImage);
                }else{
                    $pstmt = $dbcon->prepare($sqlwithoutImage);
                }
                $pstmt->bindParam('firstname', $staff->firstname);
                $pstmt->bindParam('lastname', $staff->lastname);
                $pstmt->bindParam('gender', $staff->gender);
                $pstmt->bindParam('address', $staff->address);
                $pstmt->bindParam('city', $staff->city);
                $pstmt->bindParam('state', $staff->state);
                $pstmt->bindParam('postalCode', $staff->postalCode);
                $pstmt->bindParam('contactNo', $staff->contactNo);
                $pstmt->bindParam('dob', $staff->dob);
                $pstmt->bindParam('position', $staff->position);
                $pstmt->bindParam('yearEmployed', $staff->yearEmployed);
                if(isset($staff->imagedata)){
                    $pstmt->bindParam('profilePicture',$filepath);
                }
                $pstmt->bindParam('status',$staff->status);
                $pstmt->bindParam('id', $id);
                $pstmt->execute();
                
                $pstmtU = $dbcon->prepare($sqlU);
                $pstmtU->bindParam('email', $staff->email);
                $pstmtU->bindParam('question', $staff->question);
                $pstmtU->bindParam('answer', $staff->answer);
                $pstmtU->bindParam('id', $id);
                $pstmtU->execute();
                if (isset($staff->imagedata)) {
                    if (file_put_contents('../upload/profilepicture/' . $staff->imagename, $imagefile)) {
                        if ($pstmt->rowCount() > 0 || $pstmtU->rowCount() > 0) {
                            returnAsJSON('staff', $staff, $app);
                        } else {
                            printError('Update not successful', 404, $app);
                        }
                    } else {
                        printError('Update not successful', 404, $app);
                    }
                } else {
                    if ($pstmt->rowCount() > 0 || $pstmtU->rowCount() > 0) {
                        returnAsJSON('staff', $staff, $app);
                    } else {
                        printError('Update not successful', 404, $app);
                    }
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);


/*
 * On POST /staff
 * Create new staff
 */
$app->post('/insertStaff', function() use($app) {
            $salt = "A3/-osv$0-231@#SC34zsd8fq]tj34&41dsjyj70u2%7vw4>[:l-==";
            $req = Slim\Slim::getInstance()->request();
            $staff = json_decode($req->getBody());
            $todayDate = date("Y-m-d");
            $sqlU = 'INSERT INTO users(uid, username , password , question, answer, email, registerDate,groupId)
                                VALUES("" ,:username,:password,:question,:answer,:email,:registerDate,"3");';
            include 'C:\xampp\htdocs\RailwayWeb\classes\User.php';
            $user = new User();
            $sql = 'INSERT INTO staff( icNo, uid, firstname, lastname, gender, address, city, state, postalCode, contactNo, dob, position, yearEmployed,profilePicture,status) 
                               VALUES(:icNo,:uid,:firstname,:lastname,:gender,:address,:city,:state,:postalCode,:contactNo,:dob,:position,:yearEmployed,:profilePicture,:status);';
            $imagefile = base64_decode($staff->imagedata);

            $filepath = 'upload/profilepicture/' . $staff->imagename;
            try {
                $dbcon = getConnection();

                $hashed = hash('sha256', $salt . $staff->password);
                $pstmtU = $dbcon->prepare($sqlU);
                $pstmtU->bindParam('username', $staff->username);
                $pstmtU->bindParam('password', $hashed);
                $pstmtU->bindParam('question', $staff->question);
                $pstmtU->bindParam('answer', $staff->answer);
                $pstmtU->bindParam('email', $staff->email);
                $pstmtU->bindParam('registerDate', $todayDate);
                $pstmtU->execute();
                $staff->uid = $dbcon->lastInsertId();

                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('icNo', $staff->icNo);
                $pstmt->bindParam('uid', $staff->uid);
                $pstmt->bindParam('firstname', $staff->firstname);
                $pstmt->bindParam('lastname', $staff->lastname);
                $pstmt->bindParam('gender', $staff->gender);
                $pstmt->bindParam('address', $staff->address);
                $pstmt->bindParam('city', $staff->city);
                $pstmt->bindParam('state', $staff->state);
                $pstmt->bindParam('postalCode', $staff->postalCode);
                $pstmt->bindParam('contactNo', $staff->contactNo);
                $pstmt->bindParam('dob', $staff->dob);
                $pstmt->bindParam('position', $staff->position);
                $pstmt->bindParam('yearEmployed', $staff->yearEmployed);
                $pstmt->bindParam('profilePicture', $filepath);
                $pstmt->bindParam('status', $staff->status);
                $pstmt->execute();

                $contents = '<p>Hi, ' . $staff->firstname . ' ' . $staff->lastname . '<br/>' .
                        'You have received this email due to creation of your new account was instigated by you by Railway Team</p>' .
                        '<br/>' .
                        '<p>IMPORTANT!<br/>' .
                        '-------------------------------------------<br/>' .
                        'If this email does not belong to you, please ignore.<br/>' .
                        '<p>Your new login details are as follows:<br/>' .
                        'UserID : ' . $staff->uid . '<br/>' .
                        'Username :' . $staff->username . '<br />' .
                        'Password :' . $staff->password . '<br />' .
                        '<br/>Please do not hesitate to contact us should you have any question regarding the website</p><br/><br/>' .
                        '<p>Regards, <br/>The Railway team.</p>';
                if (file_put_contents('../upload/profilepicture/' . $staff->imagename, $imagefile)) {
                    if ($user->sendEmailAccountCreation($staff->email, $contents)) {
                        if ($pstmt->rowCount() > 0 || $pstmtU->rowCount() > 0) {
                            returnAsJSON('staff', $staff, $app);
                        } else {
                            printError('Something went wrong, Please try again later.', 404, $app);
                        }
                    } else {
                        printError('Connection problem \nSomething went wrong, Please try again later.', 404, $app);
                    }
                } else {
                    $app->response()->status(404);
                    echo json_encode("Unable to create item now, please try again later.");
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /staff/:ic
 * Update data of existing staff
 */
$app->put('/updateStaffByIc/:ic', function($ic) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $staff = json_decode($req->getBody());
            $sql = 'UPDATE staff SET firstname = :firstname , lastname = :lastname , 
                    gender = :gender , address = :address , city = :city , state = :state , 
                    postalCode = :postalCode , contactNo =:contactNo ,  dob = :dob , position = :position , yearEmployed = :yearEmployed 
                    WHERE icNo = :ic';
            $sqlU = 'UPDATE users u, staff s SET u.email = :email , u.question = :question , u.answer = :answer WHERE u.uid = s.uid AND s.icNo = :ic;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('firstname', $staff->firstname);
                $pstmt->bindParam('lastname', $staff->lastname);
                $pstmt->bindParam('gender', $staff->gender);
                $pstmt->bindParam('address', $staff->address);
                $pstmt->bindParam('city', $staff->city);
                $pstmt->bindParam('state', $staff->state);
                $pstmt->bindParam('postalCode', $staff->postalCode);
                $pstmt->bindParam('contactNo', $staff->contactNo);
                $pstmt->bindParam('dob', $staff->dob);
                $pstmt->bindParam('position', $staff->position);
                $pstmt->bindParam('yearEmployed', $staff->yearEmployed);
                $pstmt->bindParam('ic', $ic);
                $pstmt->execute();

                $pstmtU = $dbcon->prepare($sqlU);
                $pstmtU->bindParam('email', $staff->email);
                $pstmtU->bindParam('question', $staff->question);
                $pstmtU->bindParam('answer', $staff->answer);
                $pstmtU->bindParam('ic', $ic);
                $pstmtU->execute();

                if ($pstmt->rowCount() > 0 || $pstmtU->rowCount() > 0) {
                    returnAsJSON('staff', $staff, $app);
                } else {
                    printError('Update not successful', 404, $app);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);


/*
 * On POST /login
 * login API for staff to login by id
 */
$app->post('/loginStaff', function() use($app) {
            $salt = "A3/-osv$0-231@#SC34zsd8fq]tj34&41dsjyj70u2%7vw4>[:l-==";
            $req = Slim\Slim::getInstance()->request();
            $staff = json_decode($req->getBody());
            $sql = 'SELECT s.uid, password, s.firstname, position,status FROM staff s, users u WHERE s.uid= u.uid AND s.uid=:uid';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('uid', $staff->uid);
                $pstmt->execute();
                $staffs = $pstmt->fetchObject();
                if ($staffs && $staffs->position == "Admin") {
                    $hashed = hash('sha256', $salt . $staff->password);
                    if ($hashed == $staffs->password) {
                        $result = array(
                            'login' => TRUE,
                            'position' => 'Admin',
                            'name' => $staffs->firstname,
                            'status' => $staffs->status
                        );
                        echo json_encode($result);
                    } else {
                        $result = array(
                            'login' => FALSE,
                            'err' => 'pass',
                            'message' => 'Password did not match.'
                        );
                        echo json_encode($result);
                        $app->response()->status(404);
                    }
                } else if ($staffs && $staffs->position == "Staff") {
                    $hashed = hash('sha256', $salt . $staff->password);
                    if ($hashed == $staffs->password) {
                        $result = array(
                            'login' => TRUE,
                            'position' => 'Staff',
                            'name' => $staffs->firstname,
                            'status' => $staffs->status
                        );
                        echo json_encode($result);
                    } else {
                        $result = array(
                            'login' => FALSE,
                            'err' => 'pass',
                            'message' => 'Password did not match.'
                        );
                        echo json_encode($result);
                        $app->response()->status(404);
                    }
                } else {
                    $result = array(
                        'login' => FALSE,
                        'err' => 'uid',
                        'message' => 'User not found'
                    );
                    echo json_encode($result);
                    $app->response()->status(404);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);


/*
 * On POST /login
 * login API for staff to login by username
 */
$app->post('/loginStaffByUsername', function() use($app) {
            $salt = "A3/-osv$0-231@#SC34zsd8fq]tj34&41dsjyj70u2%7vw4>[:l-==";
            $req = Slim\Slim::getInstance()->request();
            $staff = json_decode($req->getBody());
            $sql = 'SELECT s.uid, password, position FROM staff s, users u WHERE s.uid= u.uid AND u.username= :username';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('username', $staff->username);
                $pstmt->execute();
                $staffs = $pstmt->fetchObject();
                if ($staffs && $staffs->position == "Admin") {
                    $hashed = hash('sha256', $salt . $staff->password);
                    if ($hashed == $staffs->password) {
                        $result = array(
                            'login' => TRUE,
                            'position' => 'Admin'
                        );
                        echo json_encode($result);
                    } else {
                        $result = array(
                            'login' => FALSE,
                            'err' => 'pass',
                            'message' => 'Password did not match.'
                        );
                        echo json_encode($result);
                        $app->response()->status(404);
                    }
                } else if ($staffs && $staffs->position == "Staff") {
                    $hashed = hash('sha256', $salt . $staff->password);
                    if ($hashed == $staffs->password) {
                        $result = array(
                            'login' => TRUE,
                            'position' => 'Staff'
                        );
                        echo json_encode($result);
                    } else {
                        $result = array(
                            'login' => FALSE,
                            'err' => 'pass',
                            'message' => 'Password did not match.'
                        );
                        echo json_encode($result);
                        $app->response()->status(404);
                    }
                } else {
                    $result = array(
                        'login' => FALSE,
                        'err' => 'uid',
                        'message' => 'User not found'
                    );
                    echo json_encode($result);
                    $app->response()->status(404);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /forget password
 * forget Password matching
 */
$app->post('/forgetPassword', function() use($app) {
            $salt = "A3/-osv$0-231@#SC34zsd8fq]tj34&41dsjyj70u2%7vw4>[:l-==";
            $req = Slim\Slim::getInstance()->request();
            $staff = json_decode($req->getBody());
            $sql = 'SELECT s.uid, password, position, answer, question, email FROM staff s, users u WHERE s.uid= u.uid AND u.uid= :uid';
            $sqlUpdate = 'Update users SET password = :password WHERE uid = :uid;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('uid', $staff->uid);
                $pstmt->execute();
                $staffs = $pstmt->fetchObject();
                if ($staffs) {
                    $hashed = hash('sha256', $salt . $staff->password);
                    if ($staff->question == $staffs->question && $staff->answer == $staffs->answer) {
                        $pstmtU = $dbcon->prepare($sqlUpdate);
                        $pstmtU->bindParam('password', $hashed);
                        $pstmtU->bindParam('uid', $staff->uid);
                        $pstmtU->execute();

                        if ($pstmtU->rowCount() > 0) {
                            include 'C:\xampp\htdocs\RailwayWeb\classes\User.php';
                            $user = new User();
                            $contents = '<p>Hi, Staff with UserID of ' . $staffs->uid . '<br/>' .
                                    'You have received this email because a user account password recovery was instigated by you on Railway Official Website</p>' .
                                    '<br/>' .
                                    '<p>IMPORTANT!<br/>' .
                                    '-------------------------------------------<br/>' .
                                    'If you did not request this password change, please report to us IMMEDIATELY.<br/>' .
                                    '<p>The new password for your account is ' . $staff->password . '. You are adviced to periodly change your password .<br/>' .
                                    'Please do not hesitate to contact us should you have any question regarding the website</p><br/><br/>' .
                                    '<p>Regards, <br/>The Railway team.</p>';
                            if ($user->sendEmail2($staffs->email, $contents)) {
                                $result = array(
                                    'exist' => TRUE,
                                    'err' => FALSE,
                                    'message' => 'Please check your email for updated new password'
                                );
                                $app->response()->status(200);
                            } else {
                                $result = array(
                                    'exist' => TRUE,
                                    'err' => TRUE,
                                    'message' => 'email is not sent'
                                );
                                $app->response()->status(200);
                            }
                        } else {
                            $result = array(
                                'exist' => TRUE,
                                'err' => TRUE,
                                'message' => 'Email was unable to sent',
                                'email' => $hashed
                            );
                            $app->response()->status(200);
                        }
                    } else {
                        $result = array(
                            'exist' => TRUE,
                            'err' => TRUE,
                            'message' => 'Question and Answer did not match.'
                        );
                        $app->response()->status(200);
                    }
                } else {
                    $result = array(
                        'exist' => FALSE,
                        'err' => TRUE,
                        'message' => 'User not found'
                    );
                    $app->response()->status(404);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
            echo json_encode($result);
        }
);


$app->post('/changePassword', function() use($app) {
            $salt = "A3/-osv$0-231@#SC34zsd8fq]tj34&41dsjyj70u2%7vw4>[:l-==";
            $req = Slim\Slim::getInstance()->request();
            $staff = json_decode($req->getBody());
            $sql = 'SELECT s.uid, password, position, answer, question, email FROM staff s, users u WHERE s.uid= u.uid AND u.uid= :uid';
            $sqlUpdate = 'Update users SET password = :password WHERE uid = :uid;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('uid', $staff->uid);
                $pstmt->execute();
                $staffs = $pstmt->fetchObject();
                if ($staffs) {
                    $hashed = hash('sha256', $salt . $staff->nPassword);
                    $hashs = hash('sha256', $salt . $staff->cPassword);
                    if ($hashs == $staffs->password) {
                        $pstmtU = $dbcon->prepare($sqlUpdate);
                        $pstmtU->bindParam('password', $hashed);
                        $pstmtU->bindParam('uid', $staff->uid);
                        $pstmtU->execute();

                        if ($pstmtU->rowCount() > 0) {
                            include 'C:\xampp\htdocs\RailwayWeb\classes\User.php';
                            $user = new User();
                            $contents = '<p>Hi, Railway Staff with UID of ' . $staff->uid . '<br/>' .
                                    'You have received this email because a user account password recovery was instigated by you on Railway Official Website</p>' .
                                    '<br/>' .
                                    '<p>IMPORTANT!<br/>' .
                                    '-------------------------------------------<br/>' .
                                    'If you did not request this password change, please report to us IMMEDIATELY.<br/>' .
                                    '<p>The new password for your account is ' . $staff->nPassword . '. You are adviced to immediately contact us if you didnt request for password change.<br/>' .
                                    'Please do not hesitate to contact us should you have any question regarding the website</p><br/><br/>' .
                                    '<p>Regards, <br/>The Railway team.</p>';
                            if ($user->sendEmail2($staffs->email, $contents)) {
                                $result = array(
                                    'exist' => TRUE,
                                    'err' => FALSE,
                                    'message' => 'Your password has successfully changed.\nPlease check your email for updated password'
                                );
                                $app->response()->status(200);
                            } else {
                                $result = array(
                                    'exist' => TRUE,
                                    'err' => TRUE,
                                    'message' => 'Your password is not successfully changed.\nConnection Error.\nPlease try again later.'
                                );
                                $app->response()->status(200);
                            }
                        } else {
                            $result = array(
                                'exist' => TRUE,
                                'err' => TRUE,
                                'message' => 'System connection problem,\n unable to proceed to change your password now'
                            );
                            $app->response()->status(200);
                        }
                    } else {
                        $result = array(
                            'exist' => TRUE,
                            'err' => TRUE,
                            'message' => 'Current password is invalid.'
                        );
                        $app->response()->status(200);
                    }
                } else {
                    $result = array(
                        'exist' => FALSE,
                        'err' => TRUE,
                        'message' => 'User not found'
                    );
                    $app->response()->status(404);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
            echo json_encode($result);
        }
);

$app->get('/routePopulate/:rid', function() use($app) {
            $dbcon = getConnection();
            try {
                $sql = 'SELECT * FROM route;';
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $route = $pstmt->fetchAll(PDO::FETCH_OBJ);
                closeConnection($dbcon);
                returnAsJSON($route, $app);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /station/:sid
 * Update station's details with specify sid
 */


$app->get('/originPopulate/:rid', function($rid) use($app) {
            $dbcon = getConnection();

            try {
                $sql = 'SELECT s.sid, s.stationName FROM station s, stationinroute sr WHERE rid = :rid AND sr.sid = s.sid;';
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('rid', $rid);
                $pstmt->execute();
                $route = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                closeConnection($dbcon);
                returnAsJSON($route, $app);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->get('/destPopulate/:rid/:sid', function($rid, $sid) use($app) {
            $dbcon = getConnection();

            if ($rid % 2 == 0) {
                //KL-IPOH
                $sql = 'SELECT s.sid, s.stationName FROM station s, stationinroute sr 
                    WHERE rid = :rid AND sr.sid > :sid AND sr.sid = s.sid ORDER BY s.sid ASC;';
            } else {
                //IPOH-KL
                $sql = 'SELECT s.sid, s.stationName FROM station s, stationinroute sr 
                    WHERE rid = :rid AND sr.sid < :sid AND sr.sid = s.sid ORDER BY s.sid DESC;';
            }

            try {
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('rid', $rid);
                $pstmt->bindParam('sid', $sid);
                $pstmt->execute();
                $route = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                closeConnection($dbcon);
                returnAsJSON($route, $app);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);


$app->get('/getStationIdByStationName/:sName', function($sName) use($app) {
            $dbcon = getConnection();
            $str = str_split($sName);
            $size = sizeof($str);
            $nName = null;

            for ($i = 0; $i < $size; $i++) {
                if ($str[$i] == "%") {
                    $str[$i] = " ";
                }
                $nName .= $str[$i];
            }
            //var_dump($sName);
            $sql = 'SELECT sid FROM station WHERE stationName = :sName';

            try {
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('sName', $nName);
                $pstmt->execute();
                $route = $pstmt->fetch();
                closeConnection($dbcon);
                returnAsJSON($route, $app);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);


$app->get('/getDestinationStationIdByStationName/:sName', function($sName) use($app) {
            $dbcon = getConnection();
            $str = str_split($sName);
            $size = sizeof($str);
            $nName = null;

            for ($i = 0; $i < $size; $i++) {
                if ($str[$i] == "%") {
                    $str[$i] = " ";
                }
                $nName .= $str[$i];
            }
            $sql = 'SELECT sid FROM station WHERE stationName = :sName';

            try {
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('sName', $nName);
                $pstmt->execute();
                $route = $pstmt->fetch();
                closeConnection($dbcon);
                returnAsJSON($route, $app);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);



$app->post('/obtainSchedule', function() use($app) {

            $req = Slim\Slim::getInstance()->request();
            $route = json_decode($req->getBody());
            $sqlOrigin = 'SELECT sr.sid, shid, stationName, travelTime, fare, routeName, scheduleName, departureTime FROM station st, stationinroute sr,
                            route r, schedule s WHERE st.sid = sr.sid AND sr.rid = r.rid AND r.rid = s.rid AND sr.sid = :originid AND s.rid = :routeid AND timestamp(:dates,s.departureTime) > NOW();';

            $sqlDestin = 'SELECT sr.sid, shid, stationName, travelTime, fare, routeName, scheduleName, departureTime FROM station st, stationinroute sr,
                            route r, schedule s WHERE st.sid = sr.sid AND sr.rid = r.rid AND r.rid = s.rid AND sr.sid = :destinid AND s.rid = :routeid AND timestamp(:dates,s.departureTime) > NOW();';

            try {
                $dbcon = getConnection();
                $pstmtOrigin = $dbcon->prepare($sqlOrigin);
                $pstmtOrigin->bindParam('routeid', $route->routeid);
                $pstmtOrigin->bindParam('originid', $route->originid);
                $pstmtOrigin->bindParam('dates', $route->selectedDate);
                $pstmtOrigin->execute();
                $infoOrigin = $pstmtOrigin->fetchAll(PDO::FETCH_ASSOC);


                $pstmtDestin = $dbcon->prepare($sqlDestin);
                $pstmtDestin->bindParam('routeid', $route->routeid);
                $pstmtDestin->bindParam('destinid', $route->destinid);
                $pstmtDestin->bindParam('dates', $route->selectedDate);
                $pstmtDestin->execute();
                $infoDestin = $pstmtDestin->fetchAll(PDO::FETCH_ASSOC);

                if ($pstmtOrigin->rowCount() > 0 && $pstmtDestin->rowCount() > 0) {
                    $result = array(
                        'infoOrigin' => $infoOrigin,
                        'infoDestin' => $infoDestin
                    );
                    returnAsJSON($result, $app);
                } else {
                    printError('Unable to get data', 404, $app);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /station/:sid
 * Update station details
 * 
 */
$app->put('/station/:sid', function($sid) use ($app) {
            $req = Slim\Slim::getInstance()->request();
            $station = json_decode($req->getBody());
            $sql = 'UPDATE station SET stationName = :name, location = :location, status = :status WHERE sid = :sid;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('sid', $sid);
                $pstmt->bindParam('name', $station->name);
                $pstmt->bindParam('location', $station->location);
                $pstmt->bindParam('status', $station->status);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /route/station/:rid
 * Get all stations that currently in the route
 */
$app->get('/route/station/:rid', function($rid) use($app) {
            $sql = 'SELECT sr.sid, stationName 
                FROM stationinroute sr, station s, route r 
                WHERE r.rid = :rid AND sr.rid = r.rid 
                AND sr.sid = s.sid AND s.status = "ACV";';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('rid', $rid);
                $pstmt->execute();
                $routestation = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($routestation) {
                    $app->response()->status(200);
                    echo json_encode($routestation);
                } else {
                    $app->response()->status(404);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /station/notin/:rid
 * Get rest of station that are not in that route
 */
$app->get('/station/notin/:rid', function($rid) use($app) {
            $sql = 'SELECT sid, stationName 
                FROM station 
                WHERE sid NOT IN (
                SELECT sid FROM stationinroute 
                WHERE rid = :rid)';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('rid', $rid);
                $pstmt->execute();
                $stations = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($stations) {
                    $app->response()->status(200);
                    echo json_encode($stations);
                } else {
                    $app->response()->status(200);
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On PUT /stationfare/:rid
 * Update station's fare
 */
$app->put('/stationfare/:rid', function($rid) use($app) {
            $req = \Slim\Slim::getInstance()->request();
            $fares = json_decode($req->getBody());
            $sql = 'UPDATE stationinroute SET fare = ';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $sql .= $fares->query;
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('rid', $rid);
                $pstmt->execute();
                if ($pstmt->rowCount() >= 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /staffDetail
 * Get all available discount regardless of its expire date
 */
$app->get('/staffDetail', function() use($app) {
            $sql = "SELECT * FROM Staff s , Users u WHERE s.uid = u.uid;";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $staff = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($staff) {
                    $app->response()->status(200);
                    echo json_encode($staff);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'No staff detail found.'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);


/*
 * On GET /staffDetailById
 * Get all available discount regardless of its expire date
 */
$app->get('/staffDetailById/:uid', function($uid) use($app) {
            $sql = "SELECT * FROM Staff s , Users u WHERE s.uid = u.uid AND s.uid=:uid;";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('uid', $uid);
                $pstmt->execute();
                $discount = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($discount) {
                    $app->response()->status(200);
                    echo json_encode($discount);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'Staff detail not found.'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);


/*
 * On GET /staffDetailByIc
 *
 */
$app->get('/staffDetailByIc/:ic', function($ic) use($app) {
            $sql = "SELECT * FROM Staff s , Users u WHERE s.uid = u.uid AND s.icNo=:ic;";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('ic', $ic);
                $pstmt->execute();
                $discount = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($discount) {
                    $app->response()->status(200);
                    echo json_encode($discount);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'Staff detail not found.'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /coach Map
 * Get the seatId and No by coach
 */
$app->get('/coachMap/:coachId', function($coachId) use($app) {
            $sql = "SELECT * FROM seat WHERE coach =:coachId";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('coachId', $coachId);
                $pstmt->execute();
                $coach = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($coach) {
                    $app->response()->status(200);
                    echo json_encode($coach);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'Coach not found'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->get('/coachMapBooked/:coachId/:shid/:bookedDate', function($coachId, $shid, $bookedDate) use($app) {
            $req = Slim\Slim::getInstance()->request();
            $coach = json_decode($req->getBody());
            $sql = "SELECT bookedseat.seatid FROM seat LEFT JOIN bookedseat ON 
                    seat.seatid = bookedseat.seatid WHERE coach =:coachId
                    AND bookedDate =:bookedDate
                    AND shid =:shid AND bookedseat.status IN ('AMD','BKD','PAD');";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('coachId', $coachId);
                $pstmt->bindParam('shid', $shid);
                $pstmt->bindParam('bookedDate', $bookedDate);
                $pstmt->execute();
                $coachs = $pstmt->fetchAll(PDO::FETCH_ASSOC);
//                if ($coachs) {
                $app->response()->status(200);
                echo json_encode($coachs);
//                } else {
//                    //$app->response()->status(404);
//                    echo json_encode(array(
//                        'error' => true,
//                        'message' => 'Coach not found'
//                    ));
//                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->get('/scheduleIdByName/:scheduleName', function($scheduleName) use($app) {
            $sql = "SELECT shid FROM schedule where scheduleName = :scheduleName";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('scheduleName', $scheduleName);
                $pstmt->execute();
                $coach = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($coach) {
                    $app->response()->status(200);
                    echo json_encode($coach);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'Schedule not found'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /discount
 * Get all available discount regardless of its expire date
 */
$app->get('/discounts', function() use($app) {
            $sql = "SELECT discountId, type, rate, endDate FROM discountrate WHERE discountId != 1 AND status != 'DEL' AND endDate >= CURDATE();";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $discount = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($discount) {
                    $app->response()->status(200);
                    echo json_encode($discount);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'No discount promotion found.'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On INSERT /ticket/:id
 * Insert new ticket
 */

$app->post('/createTicket', function() use ($app) {
            $salt = "A3/-osv$0-231@#SC34zsd8fq]tj34&41dsjyj70u2%7vw4>[:l-==";

            $req = Slim\Slim::getInstance()->request();
            $ticket = json_decode($req->getBody());
            $sql = 'INSERT INTO ticket(departureDate, departure, arrival, origin, destination, class, fare, status, discountId, shid) 
                    VALUES (:departureDate, :departure, :arrival, :origin, :destination, :class, :fare, :status, :discountId, :shid);';
            $sqlSeat = 'INSERT INTO bookedseat(shid,seatid,status,bookedDate,referenceNo) VALUES (:sshid,:sseatid,:status,:bookedDate,:sreferrenceNo);';
            $sqlQrCode = 'INSERT INTO ticketqrcode(code,referenceNo) VALUES (:code,:referenceNo);';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('departureDate', $ticket->departureDate);
                $pstmt->bindParam('departure', $ticket->departure);
                $pstmt->bindParam('arrival', $ticket->arrival);
                $pstmt->bindParam('origin', $ticket->origin);
                $pstmt->bindParam('destination', $ticket->destination);
                $pstmt->bindParam('class', $ticket->classes);
                $pstmt->bindParam('fare', $ticket->fare);
                $pstmt->bindParam('status', $ticket->status);
                $pstmt->bindParam('discountId', $ticket->discountId);
                $pstmt->bindParam('shid', $ticket->shid);
                $pstmt->execute();

                $referenceNo = $dbcon->lastInsertId();
                $pstmtSeat = $dbcon->prepare($sqlSeat);
                $pstmtSeat->bindParam('sshid', $ticket->sshid);
                $pstmtSeat->bindParam('sseatid', $ticket->sseatid);
                $pstmtSeat->bindParam('bookedDate', $ticket->departureDate);
                $pstmtSeat->bindParam('status', $ticket->status);
                $pstmtSeat->bindParam('sreferrenceNo', $referenceNo);
                $pstmtSeat->execute();

                $param = $referenceNo . ' ' . $ticket->departureDate . ' ' . $ticket->departure . ' ' . $ticket->arrival . ' ' . $ticket->origin . ' ' . $ticket->destination . ' ' . $ticket->shid . ' ' . $ticket->fare;
                $rehash = hash('sha256', $salt . $param);

                $pstmtQR = $dbcon->prepare($sqlQrCode);
                $pstmtQR->bindParam('code', $rehash);
                $pstmtQR->bindParam('referenceNo', $referenceNo);
                $pstmtQR->execute();

                if ($pstmt->rowCount() > 0 && $pstmtSeat->rowCount() > 0 && $pstmtQR->rowCount() > 0) {
                    $app->response()->status(200);
                    echo json_encode(array(
                        'referenceNo' => $referenceNo
                    ));
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->get('/getScheduleOriginPrice/:routeId/:originId', function($routeId, $originId) use($app) {
            $sqlOrigin = 'SELECT fare FROM stationinroute WHERE rid=:rid AND sid=:originId;';
            //$sqlDestination = 'SELECT fare FROM stationinroute WHERE rid=:rid AND sid=:destinationId';

            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sqlOrigin);
                $pstmt->bindParam('originId', $originId);
                $pstmt->bindParam('rid', $routeId);
                $pstmt->execute();
                $ticketPrice = $pstmt->fetchAll(PDO::FETCH_ASSOC);

                if ($ticketPrice) {
                    $app->response()->status(200);
                    echo json_encode($ticketPrice);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'Fare not found'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->get('/getScheduleDestinationPrice/:routeId/:destinationId', function($routeId, $destinationId) use($app) {

            $sqlDestination = 'SELECT fare FROM stationinroute WHERE rid=:rid AND sid=:destinationId';

            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sqlDestination);
                $pstmt->bindParam('destinationId', $destinationId);
                $pstmt->bindParam('rid', $routeId);
                $pstmt->execute();
                $ticketPrice = $pstmt->fetchAll(PDO::FETCH_ASSOC);

                if ($ticketPrice) {
                    $app->response()->status(200);
                    echo json_encode($ticketPrice);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'Fare not found'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /reporting
 * Obtain data necessary for reporting
 */
$app->post('/reporting', function() use($app) {
            $req = \Slim\Slim::getInstance()->request();
            $query = json_decode($req->getBody());
            $sql = $query->query;
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $data = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($data) {
                    $app->response()->status(200);
                    echo json_encode($data);
                } else {
                    $app->response()->status(200);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);


$app->get('/ticketDetails/:code', function($code) use($app) {
            $sql = "SELECT * FROM ticketqrcode qrcode, bookedSeat bs WHERE bs.referenceNo = qrcode.referenceNo AND qrcode.code = :code";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('code', $code);
                $pstmt->execute();
                $ticket = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($ticket) {
                    $app->response()->status(200);
                    echo json_encode($ticket);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'Ticket detail not found.'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->get('/getRouteByShId/:shid', function($shid) use($app) {
            $sql = "SELECT rid FROM schedule WHERE shid = :shid";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('shid', $shid);
                $pstmt->execute();
                $route = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($route) {
                    $app->response()->status(200);
                    echo json_encode($route);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'route not found.'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->get('/getOriginDestinationName/:refNo', function($refNo) use($app) {
            $sql = "SELECT origin, destination FROM ticket WHERE referenceNo=:referenceNo;";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('referenceNo', $refNo);
                $pstmt->execute();
                $ticket = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($ticket) {
                    $app->response()->status(200);
                    echo json_encode($ticket);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'route not found.'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /train
 * Create a new train in database
 */
$app->post('/amendBookedSeat', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $ticket = json_decode($req->getBody());
            $sql = 'UPDATE bookedseat SET shid = :shid,status =:status, seatid =:seatid, bookedDate = :bookedDate WHERE referenceNo = :refNo ;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('shid', $ticket->shid);
                $pstmt->bindParam('seatid', $ticket->seatid);
                $pstmt->bindParam('status', $ticket->status);
                $pstmt->bindParam('bookedDate', $ticket->bookedDate);
                $pstmt->bindParam('refNo', $ticket->referenceNo);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /train
 * Create a new train in database
 */
$app->post('/amendTicket', function() use($app) {
            $salt = "A3/-osv$0-231@#SC34zsd8fq]tj34&41dsjyj70u2%7vw4>[:l-==";
            $req = Slim\Slim::getInstance()->request();
            $ticket = json_decode($req->getBody());
            $sql = 'UPDATE Ticket SET departureDate = :departureDate, departure = :departure, class=:class, status=:status, shid=:shid WHERE referenceNo = :referenceNo;';
            $sqlQrCode = 'UPDATE TicketQrCode SET code = :code WHERE referenceNo = :referenceNo;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('shid', $ticket->shid);
                $pstmt->bindParam('departure', $ticket->departure);
                $pstmt->bindParam('status', $ticket->status);
                $pstmt->bindParam('class', $ticket->class);
                $pstmt->bindParam('departureDate', $ticket->departureDate);
                $pstmt->bindParam('referenceNo', $ticket->referenceNo);
                $pstmt->execute();

                $param = $ticket->referenceNo . ' ' . $ticket->departureDate . ' 
                    ' . $ticket->departure . '
                        ' . $ticket->arrival . ' 
                            ' . $ticket->origin . ' 
                                ' . $ticket->destination . ' 
                                    ' . $ticket->shid . ' 
                                        ' . $ticket->fare;
                $rehash = hash('sha256', $salt . $param);

                $pstmtQR = $dbcon->prepare($sqlQrCode);
                $pstmtQR->bindParam('code', $rehash);
                $pstmtQR->bindParam('referenceNo', $ticket->referenceNo);
                $pstmtQR->execute();


                if ($pstmt->rowCount() > 0 && $pstmtQR->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->get('/ticketCancelDetails/:code', function($code) use($app) {
            $sql = "SELECT * FROM ticket t, ticketqrcode qr WHERE qr.referenceNo = t.referenceNo AND t.departureDate>=CURDATE() AND qr.code = :code";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('code', $code);
                $pstmt->execute();
                $ticket = $pstmt->fetchObject();
                if ($ticket) {
                    $app->response()->status(200);
                    echo json_encode($ticket);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'Ticket detail not found.'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->get('/ticketGetOldFare/:refNo', function($refNo) use($app) {
            $sql = "SELECT fare FROM ticket WHERE referenceNo = :referenceNo";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('referenceNo', $refNo);
                $pstmt->execute();
                $ticket = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($ticket) {
                    $app->response()->status(200);
                    echo json_encode($ticket);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'Ticket detail not found.'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /bookedSeat
 * Delete seat from bookedSeat
 */
$app->post('/cancelBookedSeat', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $ticket = json_decode($req->getBody());
            $sql = 'UPDATE bookedSeat SET status=:status WHERE referenceNo =:referenceNo;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('status', $ticket->status);
                $pstmt->bindParam('referenceNo', $ticket->referenceNo);
                $pstmt->execute();
                if ($pstmt->rowCount() >= 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On POST /bookedSeat
 * Delete seat from bookedSeat
 */
$app->post('/cancelUpdateTicket', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $ticket = json_decode($req->getBody());
            $sql = 'UPDATE ticket SET status =:status , fare =:fare WHERE referenceNo =:referenceNo;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('status', $ticket->status);
                $pstmt->bindParam('fare', $ticket->fare);
                $pstmt->bindParam('referenceNo', $ticket->referenceNo);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->get('/TicketQrCodeByRefNo/:refNo', function($refNo) use($app) {
            $sql = "SELECT code FROM ticketqrcode WHERE refNo=:referenceNo ";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('referenceNo', $refNo);
                $pstmt->execute();
                $ticket = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($ticket) {
                    $app->response()->status(200);
                    echo json_encode($ticket);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'Ticket detail not found.'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->get('/GetTicketCodeByRef/:refNo', function($refNo) use($app) {
            $sql = "SELECT code FROM ticketqrcode WHERE referenceNo=:referenceNo ";
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('referenceNo', $refNo);
                $pstmt->execute();
                $ticket = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($ticket) {
                    $app->response()->status(200);
                    echo json_encode($ticket);
                } else {
                    $app->response()->status(404);
                    echo json_encode(array(
                        'error' => true,
                        'message' => 'Ticket detail not found.'
                    ));
                }
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->post('/insertSalesLog', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $sales = json_decode($req->getBody());
            $sql = 'INSERT INTO saleslog(slogid,salesAmount,salesDate,madeBy,transactionType) VALUES("",:amount,NOW(),:madeBy,:type)';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('amount', $sales->amount);
                $pstmt->bindParam('madeBy', $sales->madeBy);
                $pstmt->bindParam('type', $sales->type);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
); 

$app->post('/insertRefundLog', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $sales = json_decode($req->getBody());
            $sql = 'INSERT INTO refundlog(rlogid,referenceNo,refundAmount,refundDate) VALUES("",:referenceNo,:amount,NOW())';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('amount', $sales->amount);
                $pstmt->bindParam('referenceNo', $sales->referenceNo);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /train
 * Get all train regardless of its status;
 */
$app->get('/member/state', function() use($app) {
            $sql = 'SELECT DISTINCT state FROM member;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $member = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($member) {
                    $app->response()->status(200);
                    echo json_encode($member);
                } else {
                    $app->response()->status(404);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);

/*
 * On GET /train
 * Get all train regardless of its status;
 */
$app->get('/member/city', function() use($app) {
            $sql = 'SELECT DISTINCT city FROM member;';
            try {
                $dbcon = getConnection();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->execute();
                $member = $pstmt->fetchAll(PDO::FETCH_ASSOC);
                if ($member) {
                    $app->response()->status(200);
                    echo json_encode($member);
                } else {
                    $app->response()->status(404);
                }
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                printError($ex->getMessage(), 500, $app);
            }
        }
);


$app->post('/getticket/salesamount', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $ticket = json_decode($req->getBody());
            $sql = 'SELECT SUM(fare) AS total FROM ticket WHERE datePurchased between :start AND :end;';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('start', $ticket->start);
                $pstmt->bindParam('end', $ticket->end);
                $pstmt->execute();
                $tickets = $pstmt->fetchObject();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                    echo json_encode($tickets);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);

$app->post('/insert/cashreconcilation', function() use($app) {
            $req = Slim\Slim::getInstance()->request();
            $info = json_decode($req->getBody());
            $sql = 'INSERT into cashreconciliationlog(crlogid,cashCollected,cashRecorded,differences,logDate,personInCharge)
                                                VALUES("",:cashCollected,:cashRecorded,:differences,:logDate,:personInCharge);';
            try {
                $dbcon = getConnection();
                $dbcon->beginTransaction();
                $pstmt = $dbcon->prepare($sql);
                $pstmt->bindParam('cashCollected',$info->cashCollected);
                $pstmt->bindParam('cashRecorded', $info->cashRecorded);
                $pstmt->bindParam('differences', $info->differences);
                $pstmt->bindParam('logDate', $info->logDate);
                $pstmt->bindParam('personInCharge', $info->personInCharge);
                $pstmt->execute();
                if ($pstmt->rowCount() > 0) {
                    $app->response()->status(200);
                } else {
                    $app->response()->status(404);
                }
                $dbcon->commit();
                closeConnection($dbcon);
            } catch (PDOException $ex) {
                $dbcon->rollBack();
                printError($ex->getMessage(), 500, $app);
            }
        }
);


$app->run();

function getConnection() {
    $dbhost = '127.0.0.1';
    $dbuser = 'root';
    $dbpass = '';
    $dbname = 'railway';
    $dbh = new PDO("mysql:host=$dbhost;port=3306;dbname=$dbname", $dbuser, $dbpass);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $dbh;
}

function closeConnection($dbcon) {
    $dbcon = null;
}

function returnAsJSON($data, $app) {
    if ($data) {
        echo json_encode($data);
    } else {
        $app->response()->status(404);
    }
}

function printError($message, $code, $app) {
    echo '{"error": true,"message": "' . $message . '"}';
    $app->response()->status($code);
}

?>
